package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.AORef;

import util.DBHelper;

public class AORefDAO {
	
	public void insertAORef(Statement statement, ArrayList<AORef> aoRefArr, String tpid) throws SQLException {
		AORef aoRef = null;
		for (int i = 0; i < aoRefArr.size(); i++) {
			aoRef = aoRefArr.get(i);
			statement.execute("insert into aoref values('" + tpid + "', " + aoRef.getAoID() + ", '" +
					aoRef.getActivityPurpose() + "', '" + aoRef.getExistentFoundation() + "', '" +
					aoRef.getPracticalBehavior() + "', " + aoRef.isImitation() + ", " +
					aoRef.isUnderstanding() + ", " + aoRef.isExpression() + ", " + 
					aoRef.isApplication() + ")");
		}
	}
	
	public void deleteAORef(Statement statement, String tpid) throws SQLException {
		statement.execute("delete from aoref where TPID='" + tpid + "'");
	}
	
	public ArrayList<AORef> getAORefArrByTPID(String tpid) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		ArrayList<AORef> al = new ArrayList<AORef>();
		AORef aoRef = null;
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from aoref where TPID = '" + tpid + "'");
		while (rs.next()) {
			int i = 1;
			aoRef = new AORef();
			aoRef.setTpID(rs.getString(i++));
			aoRef.setAoID(rs.getInt(i++));
			aoRef.setActivityPurpose(rs.getString(i++));
			aoRef.setExistentFoundation(rs.getString(i++));
			aoRef.setPracticalBehavior(rs.getString(i++));
			aoRef.setImitation(rs.getInt(i++));
			aoRef.setUnderstanding(rs.getInt(i++));
			aoRef.setExpression(rs.getInt(i++));
			aoRef.setApplication(rs.getInt(i++));
			al.add(aoRef);
		}
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}

	public void updateAORef(Statement statement, ArrayList<AORef> aoRefArr, String tpid) throws SQLException {
		AORef aoRef = null;
		for (int i = 0; i < aoRefArr.size(); i++) {
			aoRef = aoRefArr.get(i);
			statement.execute("update aoref set ActivityPurpose = '" + aoRef.getActivityPurpose() + 
					"', ExistentFoundation = '" + aoRef.getExistentFoundation() + 
					"', PracticalBehavior = '" + aoRef.getPracticalBehavior() + "', Imitation = " + 
					aoRef.isImitation() + ", Understanding = " + aoRef.isUnderstanding() + ", Expression = " 
					+ aoRef.isExpression() + ", Application = " + aoRef.isApplication() + 
					" where TPID = '" + tpid + "' and AOID = " + aoRef.getAoID() + "");
		}
	}
}
